use chrono::DateTime;
use sqlx::PgPool;

use crate::hot::{Ad, Gov, Hot};

//# region DB adtable
pub async fn add_ad(pool: &PgPool, ad: Ad) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        INSERT INTO public.wb_ad(
            id, key_word, ad_type, start_time, end_time)
            VALUES ($1, $2, $3, $4, $5)
            RETURNING ad_id;
        "#,
    )
    .bind(ad.id)
    .bind(ad.key_word)
    .bind(ad.ad_type)
    .bind(DateTime::from_timestamp(ad.start_time, 0).unwrap())
    .bind(DateTime::from_timestamp(ad.end_time, 0).unwrap())
    .fetch_one(pool)
    .await?;
    Ok(())
}
pub async fn in_ad(pool: &PgPool, id: &i64) -> Result<bool, sqlx::Error> {
    let mut ad_in_db = false;
    let rows = sqlx::query(
        r#"
        SELECT ad_id, id, key_word, ad_type, start_time, end_time
	    FROM public.wb_ad 
        WHERE id=$1;
        "#,
    )
    .bind(id.clone())
    .fetch_all(pool)
    .await?;
    if rows.len() > 0 {
        ad_in_db = true
    }
    Ok(ad_in_db)
}
//# endregion
//# region DB govtable
pub async fn add_gov(pool: &PgPool, gov: Gov) -> Result<u64, sqlx::Error> {
    //应该返回影响的行数
    // let row = sqlx::query_as::<_, TbGov>(
    //     r#"
    //     INSERT INTO public.wb_gov(
    //         key_word, stime)
    //         VALUES ( $1, $2)
    //         RETURNING govid;
    //     "#,
    // )
    // .bind(gov.key_word)
    // .bind(DateTime::from_timestamp(gov.stime, 0).unwrap())
    // .fetch_one(pool)
    // .await?;
    let result = sqlx::query(
        r#"
        INSERT INTO public.wb_gov(
            key_word, stime)
            VALUES ( $1, $2)
            RETURNING govid;
        "#,
    )
    .bind(gov.key_word)
    .bind(DateTime::from_timestamp(gov.stime, 0).unwrap())
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}
pub async fn in_gov(pool: &PgPool, gov_key_word: &String) -> Result<bool, sqlx::Error> {
    let mut gov_in_db: bool = false;
    let rows = sqlx::query(
        r#"
        SELECT govid
        FROM public.wb_gov
        WHERE key_word=$1
        "#,
    )
    .bind(gov_key_word)
    .fetch_all(pool)
    .await?;
    if rows.len() > 0 {
        gov_in_db = true;
    }
    Ok(gov_in_db)
}
//#endregion

// #region hot db
pub async fn in_hot(pool: &PgPool, key_word: &String) -> Result<bool, sqlx::Error> {
    let mut hot_in_db = false;
    let rows = sqlx::query(
        r#"
        SELECT hotid
	    FROM public.wb_hot 
        WHERE key_word=$1;
        "#,
    )
    .bind(key_word)
    .fetch_all(pool)
    .await?;
    if rows.len() > 0 {
        hot_in_db = true
    }
    Ok(hot_in_db)
}
pub async fn add_hot(pool: &PgPool, hot: Hot) -> Result<u64, sqlx::Error> {
    let result = sqlx::query(
        r#"
        INSERT INTO public.wb_hot(
            key_word, category, channel_type, star_name, onboard_time, ontop_time, outrank_time, realpos, raw_hot)
            VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9);
        "#,
    )
    .bind(hot.key_word)
    .bind(hot.category)
    .bind(hot.channel_type)
    .bind(hot.star_name)
    .bind(DateTime::from_timestamp(hot.onboard_time,0 ).unwrap())
    .bind(DateTime::from_timestamp(hot.ontop_time,0 ).unwrap())
    .bind(DateTime::from_timestamp(hot.outrank_time,0 ).unwrap())
    .bind(hot.realpos)
    .bind(hot.raw_hot)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}
//更新语句，只有排名上升和热度上升满足才更新
pub async fn updata_hot(pool: &PgPool, hot: &Hot) -> Result<u64, sqlx::Error> {
    let respos = sqlx::query(
        r#"
        UPDATE public.wb_hot
	        SET ontop_time=$1, outrank_time=$2, realpos=$3
	        WHERE key_word=$4 and realpos>$3;
        "#,
    )
    .bind(DateTime::from_timestamp(hot.ontop_time, 0).unwrap())
    .bind(DateTime::from_timestamp(hot.outrank_time, 0).unwrap())
    .bind(hot.realpos)
    .bind(hot.key_word.clone())
    .execute(pool)
    .await?;
    let reshot = sqlx::query(
        r#"
        UPDATE public.wb_hot
	        SET raw_hot=$1
	        WHERE key_word=$2 and raw_hot<$1;
        "#,
    )
    .bind(hot.raw_hot)
    .bind(hot.key_word.clone())
    .execute(pool)
    .await?;
    Ok(respos.rows_affected() + reshot.rows_affected())
}
// #endregion
